package com.fpt.practice.conndb;

import java.io.IOException;
import java.sql.*;
import com.fpt.practice.utility.Utility;

/**
 * Thực hiện các truy vấn đến CSDL Oracle
 * @author Administrator
 */
public class ConnectDB {

    /**
     * Get connection oracle database
     * @return connection to database
     * @throws SQLException 
     */
    public static Connection getConnORA() throws SQLException, IOException {

        //Load info database oracle         
        String config_ora[] = Utility.getConfigORA("configORA.ora").split(",");
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = config_ora[0];
            String username = config_ora[1];
            String password = config_ora[2];
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException ex) {
            throw new SQLException(ex.getMessage());
        } catch (ClassNotFoundException cnfe) {
            System.err.println("Driver not found.");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }


    /**
     * Thực hiện lấy thông tin CQT
     * @param sql
     * @throws SQLException 
     * @return short_name, ma_cqt
     */
    public static String getInfoCQT(String sql) throws SQLException, IOException {
        String info = "";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = getConnORA();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next()) {
                info = rset.getString("tax_code") + "," + rset.getString("short_name") + "," + rset.getString("tax_model");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            rset.close();
            stmt.close();
//            conn.close();
        }
        return info;
    }

    /**
     * Lấy thông tin số lượng dữ liệu đã chuyển đổi
     * @param short_name
     * @throws SQLException 
     * @return number convert
     */
    public static int[] getNumCV(String short_name) throws SQLException, IOException {
        int cqt_cv[] = new int[3];
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = getConnORA();
            stmt = conn.createStatement();
            String sql = "SELECT count(type_data) num_convert FROM tb_cqt_convert a where a.short_name = '" + short_name
                    + "' and  a.type_data in ('NO','PS','TK') group by type_data order by a.type_data";
            rset = stmt.executeQuery(sql);
            int i = 0;
            while (rset.next()) {
                cqt_cv[i] = rset.getInt("num_convert");
                i++;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            rset.close();
            stmt.close();
//            conn.close();
        }
        return cqt_cv;
    }
    
    
    public static void main(String[] args) throws IOException, SQLException {
        
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        String sql = "select * from tb_lst_taxo where rownum <= 10";
        try {
            conn = getConnORA();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next()) {
                System.out.println("short_name: "+rset.getString("short_name"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            rset.close();
            stmt.close();
            conn.close();
        }
        
    }

}
